
CREATE TABLE `mid_rule1`(
  `CARDUSER_ID` decimal(19,0),
  `CUSTOMER_ID`  decimal(19,0),
  `customer_types` decimal(10,0),
  `CUSTOMER_LEVEL` decimal(10,0),
  `GENDER` decimal(10,0),
  `NAME` char(32),
  `MOBILEPHONE` char(24),
  `ADDRESS` char(160),
  `ORDERNUM` char(128),
  `REALAMOUNT` decimal(19,0),
  `PPSORDERNUMBER` varchar(40),
  `OILCODE` varchar(255),
  `STATIONCODE` char(12) ,
  `SENDTIME` timestamp ,
  `MEMBERNO` varchar(255)

);



insert overwrite table mid_rule1
select A.CARDUSER_ID,B.CUSTOMER_ID,B.customer_types,A.customer_level,B.gender,A.NAME,substr(B.MOBILEPHONE,1,11),B.ADDRESS
,C.ORDERNUM,C.REALAMOUNT,C.PPSORDERNUMBER,C.OILCODE,C.STATIONCODE,C.SENDTIME,C.MEMBERNO
FROM TBL_CARDUSER AS A
JOIN TBL_CUSTOMER AS B
ON A.CUSTOMER_ID = B.CUSTOMER_ID
JOIN TBL_ORDERDETAILS AS C
ON C.CARDUSERID=A.CARDUSER_ID  and C.CUSTOMER_ID=B.CUSTOMER_ID
;
-- 支付
CREATE TABLE `mid_rule2`(
  `ORDERNUM` char(128),
  `SENDTIME` timestamp ,
  `station_id` varchar(12) ,
  `mop_name` char(20),
  `cashier_name` char(20)
);


insert overwrite table mid_rule2
select Z.transaction_number,Z.transaction_date,A.station_id,A.mop_name,Y.cashier_name
from mid.transaction_items_mop as A
join transactions_vip AS Z on A.transaction_id=Z.transaction_id and A.station_id=Z.station_id  and A.transaction_date=Z.transaction_date
JOIN mid.cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id
where  A.item_value > 0  ;



--
  CREATE TABLE `mid_rule3`(
  `CARDUSER_ID` decimal(19,0),
  `CUSTOMER_ID`  decimal(19,0),
  `customer_types` decimal(10,0),
  `CUSTOMER_LEVEL` decimal(10,0),
  `GENDER` decimal(10,0),
  `NAME` varchar(32),
  `MOBILEPHONE` varchar(24),
  `ADDRESS` varchar(160),
  `ORDERNUM` varchar(128),
  `REALAMOUNT` decimal(19,0),
  `PPSORDERNUMBER` varchar(40),
  `OILCODE` varchar(255),
  `STATIONCODE` varchar(12) ,
  `SENDTIME` timestamp ,
  `MEMBERNO` varchar(255),
  `mop_name` varchar(20),
  `cashier_name` varchar(20),
  `isNight`  int
)
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\001' ;

insert overwrite table mid_rule3
select
a.CARDUSER_ID    ,
a.CUSTOMER_ID    ,
a.customer_types ,
a.CUSTOMER_LEVEL ,
a.GENDER         ,
a.NAME           ,
a.MOBILEPHONE    ,
a.ADDRESS        ,
a.ORDERNUM       ,
a.REALAMOUNT     ,
a.PPSORDERNUMBER ,
trim(a.OILCODE)        ,
trim(a.STATIONCODE)    ,
a.SENDTIME       ,
a.MEMBERNO       ,

trim(b.mop_name),trim(b.cashier_name),
 case when (SUBSTR(b.SENDTIME,12,5)>='19:00' and SUBSTR(b.SENDTIME,12,5)<='24:00' )
           or (SUBSTR(b.SENDTIME,12,5)>='00:00' and SUBSTR(b.SENDTIME,12,5)<='07:00' )
           then 1 else 0 end  isNight
 from
mid_rule1 as a
join mid_rule2 as b
on trim(a.STATIONCODE)=trim(b.station_id) and
substr(a.SENDTIME,1,10) = substr(b.SENDTIME,1,10) and trim(a.ORDERNUM)=trim(b.ORDERNUM);


bin/sqoop export --connect jdbc:mysql://192.168.0.34:3306/result --username root --password root --table res_rule --export-dir /user/hive/warehouse/res_rule/ --input-fields-terminated-by '\001' -m 1 --input-null-string '\\N' --input-null-non-string '\\N'



CREATE TABLE `res_rule`(
    `CARDUSER_ID` decimal(19,0),
  `CUSTOMER_ID`  decimal(19,0),
  `customer_types` decimal(10,0),
  `CUSTOMER_LEVEL` decimal(10,0),
  `GENDER` decimal(10,0),
  `NAME` varchar(32),
  `MOBILEPHONE` varchar(24),
  `ADDRESS` varchar(160),
  `ORDERNUM` varchar(128),
  `REALAMOUNT` decimal(19,0),
  `PPSORDERNUMBER` varchar(40),
  `OILCODE` varchar(255),
  `STATIONCODE` varchar(12) ,
  `SENDTIME` varchar(20) ,
  `MEMBERNO` varchar(255),
  `mop_name` varchar(255),
  `cashier_name` varchar(255),

  `coupon1` varchar(255),
  `coupon2` varchar(255),
  `trade_count` int,
  `night_count` int
  )
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\001' ;

insert overwrite table res_rule
SELECT
CARDUSER_ID      ,
CUSTOMER_ID      ,
max( customer_types),
max( customer_level),
max(GENDER)           ,
max(NAME             ),
MOBILEPHONE      ,
max(ADDRESS          ),
concat_ws(',', collect_set(ORDERNUM))  ORDERNUM         ,
sum(REALAMOUNT)/100.0       ,
concat_ws(',', collect_set(PPSORDERNUMBER)) PPSORDERNUMBER   ,
concat_ws(',', collect_set(OILCODE))  OILCODE,
STATIONCODE      ,
SUBSTR(SENDTIME,1,10) SENDTIME         ,
max(MEMBERNO),
concat_ws(',', collect_set(mop_name)) mop_name         ,
concat_ws(',', collect_set(cashier_name)) cashier_name      ,
null,null,count( ORDERNUM),sum(isNight)
from mid_rule3
GROUP BY
CARDUSER_ID,CUSTOMER_ID,SUBSTR(SENDTIME,1,10),MOBILEPHONE,STATIONCODE;
